package rai.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.portlet.RenderRequest;
import javax.portlet.RenderResponse;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.log4j.Logger;

import rai.dto.AppDTO;
import rai.dto.CompanyDTO;
import rai.dto.CourseDTO;
import rai.dto.DatabaseDTO;
import rai.dto.DepartmentDTO;
import rai.dto.EmployeeDTO;
import rai.dto.FrwDTO;
import rai.dto.GradeDTO;
import rai.dto.LangDTO;
import rai.dto.ProjectDTO;
import rai.dto.TechDTO;
import rai.form.SearchInfoForm;

/**
 * DAO class for SearchInfoPortlet.
 *
 * @author Vu
 *
 */
public class SearchInfoDAO {
    /**
     * log property.
     */
    private static Logger log = Logger.getLogger("SimpleLoginDAO");
    /**
     * Get Database List.
     * @return lst of Database.
     */
    public List<DatabaseDTO> getDatabase() {
        DatabaseDTO dto;
        final List<DatabaseDTO> result = new ArrayList<DatabaseDTO>();
        Connection conn = null;
        String query = "";
        ResultSet rs = null;
        PreparedStatement pStat = null;

        try {
            // query command
            query = "select DatabaseID,Name from database";
            // get connection
            conn = rai.util.Connection.getConnection();
            // prepare statement
            pStat = conn.prepareStatement(query);
            // pStat.setInt(1, CompanyID);
            // pStat.setString(2, CompName);
            // execute query
            rs = pStat.executeQuery();
            // get result
            dto = new DatabaseDTO();
            dto.setId(0);
            dto.setSname("All");
            result.add(dto);
            while (rs.next()) {
                dto = new DatabaseDTO();
                dto.setId(rs.getInt("DatabaseID"));
                dto.setSname(rs.getString("Name"));
                result.add(dto);
            }

           
        } catch (final Exception sqlEx) {
            log.error("SearchInfoDAO.getDatabase " + sqlEx);
        } finally {
            try {
                rs.close();
                pStat.close();
                conn.close();
            } catch (final SQLException sqlEx) {
                log.error("SearchInfoDAO.getDatabase " + sqlEx);
            }
        }

        return result;
    }
    /**
     * Get List Company.
     *
     * @param request
     * @param response
     * @return List CompanyDTO
     */
    public List<CompanyDTO> getComp() {
        CompanyDTO dto;
        final List<CompanyDTO> result = new ArrayList<CompanyDTO>();
        Connection conn = null;
        String query = "";
        ResultSet rs = null;
        PreparedStatement pStat = null;

        try {
            // query command
            query = "select CompanyID,ComName from company";
            // get connection
            conn = rai.util.Connection.getConnection();
            // prepare statement
            pStat = conn.prepareStatement(query);
            // pStat.setInt(1, CompanyID);
            // pStat.setString(2, CompName);
            // execute query
            rs = pStat.executeQuery();
            // get result
            dto = new CompanyDTO();
            dto.setId(0);
            dto.setSname("All");
            result.add(dto);
            while (rs.next()) {
                dto = new CompanyDTO();
                dto.setId(rs.getInt("CompanyID"));
                dto.setSname(rs.getString("ComName"));
                result.add(dto);
            }

           
        } catch (final Exception sqlEx) {
            log.error("SearchInfoDAO.getComp " + sqlEx);
        } finally {
            try {
                rs.close();
                pStat.close();
                conn.close();
            } catch (final SQLException sqlEx) {
                log.error("SearchInfoDAO.getComp " + sqlEx);
            }
        }

        return result;
    }

    /**
     * Get List Department.
     *
     * @param CompID
     * @param request
     * @param response
     * @return List DepartmentDTO
     */
    public List<DepartmentDTO> getDep(final String CompID,
            final RenderRequest request, final RenderResponse response) {
        DepartmentDTO dto;
        final List<DepartmentDTO> result = new ArrayList<DepartmentDTO>();
        Connection conn = null;
        String query = "";
        ResultSet rs = null;
        PreparedStatement pStat = null;

        try {
            // query command
            query = "select distinct DepartID,DepartName from department";
            // get connection
            conn = rai.util.Connection.getConnection();
            if (CompID.equals("0")) {
                // prepare statement
                pStat = conn.prepareStatement(query);
            } else {
                query += " where CompanyID=?";
                // prepare statement
                pStat = conn.prepareStatement(query);
                pStat.setInt(1, Integer.parseInt(CompID));
            }

            // execute query
            rs = pStat.executeQuery();
            // get result
            dto = new DepartmentDTO();
            dto.setId(0);
            dto.setSname("All");
            result.add(dto);

            while (rs.next()) {
                dto = new DepartmentDTO();
                dto.setId(rs.getInt("DepartID"));
                dto.setSname(rs.getString("DepartName"));
                result.add(dto);
            }

        } catch (final Exception sqlEx) {
            log.error("SearchInfoDAO.getDep " + sqlEx);
        } finally {
            try {
                rs.close();
                pStat.close();
                conn.close();
            } catch (final SQLException sqlEx) {
                log.error("SearchInfoDAO.getDep " + sqlEx);
            }
        }

        return result;
    }

    /**
     * Get List of Course.
     *
     * @param DepID
     * @return
     */
    public List<CourseDTO> getCourse(final String DepID) {
        CourseDTO dto;
        final List<CourseDTO> result = new ArrayList<CourseDTO>();
        Connection conn = null;
        String query = "";
        ResultSet rs = null;
        PreparedStatement pStat = null;

        try {
            // query command
            query = "select distinct CourseID,Name from course";
            // get connection
            conn = rai.util.Connection.getConnection();
            if (DepID.equals("0")) {
                // prepare statement
                pStat = conn.prepareStatement(query);
            } else {
                query += " where DepartID=?";
                // prepare statement
                pStat = conn.prepareStatement(query);
                pStat.setInt(1, Integer.parseInt(DepID));
            }

            // execute query
            rs = pStat.executeQuery();
            // get result
            dto = new CourseDTO();
            dto.setId(0);
            dto.setSname("All");
            result.add(dto);

            while (rs.next()) {
                dto = new CourseDTO();
                dto.setId(rs.getInt("CourseID"));
                dto.setSname(rs.getString("Name"));
                result.add(dto);
            }

        } catch (final Exception sqlEx) {
            log.error("SearchInfoDAO.getCourse " + sqlEx);
        } finally {
            try {
                rs.close();
                pStat.close();
                conn.close();
            } catch (final SQLException sqlEx) {
                log.error("SearchInfoDAO.getCourse " + sqlEx);
            }
        }

        return result;
    }
    /**
     * Get Grade List.
     * @param request
     * @param response
     * @return List GradeDTO
     */
    public List<GradeDTO> getGrade() {
        GradeDTO dto;
        final List<GradeDTO> result = new ArrayList<GradeDTO>();
        Connection conn = null;
        String query = "";
        ResultSet rs = null;
        PreparedStatement pStat = null;

        try {
            // query command
            query = "select distinct Grade from courseexp";
            // get connection
            conn = rai.util.Connection.getConnection();
            // prepare statement
            pStat = conn.prepareStatement(query);

            // execute query
            rs = pStat.executeQuery();
            // get result
            dto = new GradeDTO();

            dto.setGrade("All");
            result.add(dto);

            while (rs.next()) {
                dto = new GradeDTO();
                dto.setGrade(rs.getString("Grade"));
                result.add(dto);
            }

        } catch (final Exception sqlEx) {
            log.error("SearchInfoDAO.getGrade " + sqlEx);
        } finally {
            try {
                rs.close();
                pStat.close();
                conn.close();
            } catch (final SQLException sqlEx) {
                log.error("SearchInfoDAO.getGrade " + sqlEx);
            }
        }

        return result;
    }
    /**
     * Get List Project.
     * @param DepID
     * @return List ProjectDTO
     */
    public List<ProjectDTO> getPrj(final String DepID) {
        ProjectDTO dto;
        final List<ProjectDTO> result = new ArrayList<ProjectDTO>();
        Connection conn = null;
        String query = "";
        ResultSet rs = null;
        PreparedStatement pStat = null;

        try {
            query = "select distinct ProjectID,ProjectName from project";
            // get connection
            conn = rai.util.Connection.getConnection();
            if (DepID.equals("0")) {
                // prepare statement
                pStat = conn.prepareStatement(query);
            } else {
                query += " where DepartID=?";
                // prepare statement
                pStat = conn.prepareStatement(query);
                pStat.setInt(1, Integer.parseInt(DepID));
            }

            // execute query
            rs = pStat.executeQuery();
            // get result
            // get result
            dto = new ProjectDTO();
            dto.setId(0);
            dto.setSname("All");
            result.add(dto);

            while (rs.next()) {
                dto = new ProjectDTO();
                dto.setId(rs.getInt("ProjectID"));
                dto.setSname(rs.getString("ProjectName"));
                result.add(dto);
            }

        } catch (final Exception sqlEx) {
            log.error("SearchInfoDAO.getProject " + sqlEx);
        } finally {
            try {
                rs.close();
                pStat.close();
                conn.close();
            } catch (final SQLException sqlEx) {
                log.error("SearchInfoDAO.getProject " + sqlEx);
            }
        }

        return result;
    }
    /**
     * Get Department according to CompID.
     * @param CompID
     * @param request
     * @param response
     * @return List DepartmentDTO
     */
    public List<DepartmentDTO> getDep1(final String CompID) {
        DepartmentDTO dto;
        final List<DepartmentDTO> result = new ArrayList<DepartmentDTO>();
        Connection conn = null;
        String query = "";
        ResultSet rs = null;
        PreparedStatement pStat = null;

        try {
            // query command
            query = "select distinct DepartID,DepartName from department";
            // get connection
            conn = rai.util.Connection.getConnection();
            if (CompID == "0") {
                // prepare statement
                pStat = conn.prepareStatement(query);
            } else {
                query += " where CompanyID=?";
                // prepare statement
                pStat = conn.prepareStatement(query);
                pStat.setInt(1, Integer.parseInt(CompID));
            }

            // execute query
            rs = pStat.executeQuery();
            // get result
            dto = new DepartmentDTO();
            dto.setId(0);
            dto.setSname("All");
            result.add(dto);

            while (rs.next()) {
                dto = new DepartmentDTO();
                dto.setId(rs.getInt("DepartID"));
                dto.setSname(rs.getString("DepartName"));
                result.add(dto);
            }

        } catch (final Exception sqlEx) {
            log.error("SearchInfoDAO.getDep " + sqlEx);
        } finally {
            try {
                rs.close();
                pStat.close();
                conn.close();
            } catch (final SQLException sqlEx) {
                log.error("SearchInfoDAO.getDep " + sqlEx);
            }
        }

        return result;
    }

    /**
     * Get Lang List.
     * @return List LangDTO
     */
    public List<LangDTO> getLang() {
        LangDTO dto;
        final List<LangDTO> result = new ArrayList<LangDTO>();
        Connection conn = null;
        String query = "";
        ResultSet rs = null;
        PreparedStatement pStat = null;

        try {
            // query command
            query = "select distinct LangID,Name from language";
            // get connection
            conn = rai.util.Connection.getConnection();
            // prepare statement
            pStat = conn.prepareStatement(query);
            // pStat.setInt(1, CompanyID);
            // pStat.setString(2, CompName);
            // execute query
            rs = pStat.executeQuery();
            // get result
            dto = new LangDTO();
            dto.setId(0);
            dto.setSname("All");
            result.add(dto);
            while (rs.next()) {
                dto = new LangDTO();
                dto.setId(rs.getInt("LangID"));
                dto.setSname(rs.getString("Name"));
                result.add(dto);
            }

            // request.getPortletSession().setAttribute("CompanyList", result,
            // PortletSession.APPLICATION_SCOPE);
        } catch (final Exception sqlEx) {
            log.error("SearchInfoDAO.getLang " + sqlEx);
        } finally {
            try {
                rs.close();
                pStat.close();
                conn.close();
            } catch (final SQLException sqlEx) {
                log.error("SearchInfoDAO.getLang " + sqlEx);
            }
        }

        return result;
    }
    /**
     * Get Application List.
     * @return List AppDTO
     */
    public List<AppDTO> getApp() {
        AppDTO dto;
        final List<AppDTO> result = new ArrayList<AppDTO>();
        Connection conn = null;
        String query = "";
        ResultSet rs = null;
        PreparedStatement pStat = null;

        try {
            // query command
            query = "select distinct AppID,Name from application";
            // get connection
            conn = rai.util.Connection.getConnection();
            // prepare statement
            pStat = conn.prepareStatement(query);
            // pStat.setInt(1, CompanyID);
            // pStat.setString(2, CompName);
            // execute query
            rs = pStat.executeQuery();
            // get result
            dto = new AppDTO();
            dto.setId(0);
            dto.setSname("All");
            result.add(dto);
            while (rs.next()) {
                dto = new AppDTO();
                dto.setId(rs.getInt("AppID"));
                dto.setSname(rs.getString("Name"));
                result.add(dto);
            }

            // request.getPortletSession().setAttribute("CompanyList", result,
            // PortletSession.APPLICATION_SCOPE);
        } catch (final Exception sqlEx) {
            log.error("SearchInfoDAO.getApp " + sqlEx);
        } finally {
            try {
                rs.close();
                pStat.close();
                conn.close();
            } catch (final SQLException sqlEx) {
                log.error("SearchInfoDAO.getApp " + sqlEx);
            }
        }

        return result;
    }
    /**
     * Get Framework List.
     * @return List FrwDTO
     */
    public List<FrwDTO> getFrw() {
        FrwDTO dto;
        final List<FrwDTO> result = new ArrayList<FrwDTO>();
        Connection conn = null;
        String query = "";
        ResultSet rs = null;
        PreparedStatement pStat = null;

        try {
            // query command
            query = "select distinct FrameID,Name from framework";
            // get connection
            conn = rai.util.Connection.getConnection();
            // prepare statement
            pStat = conn.prepareStatement(query);
            // pStat.setInt(1, CompanyID);
            // pStat.setString(2, CompName);
            // execute query
            rs = pStat.executeQuery();
            // get result
            dto = new FrwDTO();
            dto.setId(0);
            dto.setSname("All");
            result.add(dto);
            while (rs.next()) {
                dto = new FrwDTO();
                dto.setId(rs.getInt("FrameID"));
                dto.setSname(rs.getString("Name"));
                result.add(dto);
            }

            // request.getPortletSession().setAttribute("CompanyList", result,
            // PortletSession.APPLICATION_SCOPE);
        } catch (final Exception sqlEx) {
            log.error("SearchInfoDAO.getFrw " + sqlEx);
        } finally {
            try {
                rs.close();
                pStat.close();
                conn.close();
            } catch (final SQLException sqlEx) {
                log.error("SearchInfoDAO.getFrw " + sqlEx);
            }
        }

        return result;
    }
    /**
     * Get Tech List.
     * @return List TechDTO
     */
    public List<TechDTO> getTech() {
        TechDTO dto;
        final List<TechDTO> result = new ArrayList<TechDTO>();
        Connection conn = null;
        String query = "";
        ResultSet rs = null;
        PreparedStatement pStat = null;

        try {
            // query command
            query = "select distinct TechID,Name from technology";
            // get connection
            conn = rai.util.Connection.getConnection();
            // prepare statement
            pStat = conn.prepareStatement(query);
            // pStat.setInt(1, CompanyID);
            // pStat.setString(2, CompName);
            // execute query
            rs = pStat.executeQuery();
            // get result
            dto = new TechDTO();
            dto.setId(0);
            dto.setSname("All");
            result.add(dto);
            while (rs.next()) {
                dto = new TechDTO();
                dto.setId(rs.getInt("TechID"));
                dto.setSname(rs.getString("Name"));
                result.add(dto);
            }

            // request.getPortletSession().setAttribute("CompanyList", result,
            // PortletSession.APPLICATION_SCOPE);
        } catch (final Exception sqlEx) {
            log.error("SearchInfoDAO.getTech " + sqlEx);
        } finally {
            try {
                rs.close();
                pStat.close();
                conn.close();
            } catch (final SQLException sqlEx) {
                log.error("SearchInfoDAO.getTech " + sqlEx);
            }
        }

        return result;
    }
    /**
     * Join 2 list.
     * @param lst1
     * @param lst2
     * @return a list of Integer
     */
    private List<Integer> joinlst(final List<Integer> lst1,
            final List<Integer> lst2) {
        final List<Integer> result = new ArrayList<Integer>();
        if (lst1.size() == 0) {
            if (lst2.size() == 0) {
                return result;
            } else {
                return lst2;
            }

        } else {
            if (lst2.size() == 0) {
                return lst1;
            } else {
                for (int i = 0; i < lst1.size(); i++) {
                    if (lst2.indexOf(lst1.get(i)) >= 0) {
                        result.add(lst1.get(i));
                    }

                }
            }
        }

        return result;
    }
    /**
     * Do search action.
     * @param form
     * @return List of EmployeeDTO
     */
    public List<EmployeeDTO> getresult(final SearchInfoForm form) {
        EmployeeDTO dto;
        final List<EmployeeDTO> result = new ArrayList<EmployeeDTO>();
        Connection conn = null;
        String query = "";
        ResultSet rs = null;
        PreparedStatement pStat = null;
        boolean getlang = false;
        List<Integer> empid = new ArrayList<Integer>();
        try {
            conn = rai.util.Connection.getConnection();
            log.info("Start Create view course");
            int id = 1;

            PreparedStatement pStat1 = null;

            String query1 = "";
            query1 = "DROP VIEW IF EXISTS course_view";
            pStat1 = conn.prepareStatement(query1);
            pStat1.executeUpdate();
            pStat1 = null;
            query1 = "create view course_view as select e.EmpID,c.CourseID "
                    + "from employee e, course c, courseexp ce "
                    + "where e.EmpID=ce.EmpID and c.CourseID=ce.CourseID ";
            if (form.getGrade().equals("All")) {
                pStat1 = conn.prepareStatement(query1);
            } else {
                query1 += " and ce.Grade=?";

                pStat1 = conn.prepareStatement(query1);
                pStat1.setString(id, form.getGrade());
                id++;

            }
            if (form.getCompIssued().equals("0")) {

            } else {
                if (form.getDepIssued().equals("0")) {
                    query1 += " and c.DepartID in (select d.DepartID from department d where d.CompanyID=?)";
                    pStat1 = conn.prepareStatement(query1);
                    if (id > 1) {
                        pStat1.setString(id - 1, form.getGrade());
                    }
                    pStat1.setInt(id, Integer.parseInt(form.getCompIssued()));
                } else {
                    if (form.getCourse().equals("0")) {
                        query1 += " and c.DepartID =?";
                        pStat1 = conn.prepareStatement(query1);
                        if (id > 1) {
                            pStat1.setString(id - 1, form.getGrade());
                        }
                        pStat1
                                .setInt(id, Integer.parseInt(form
                                        .getDepIssued()));
                    } else {
                        query1 += " and c.CourseID =?";
                        pStat1 = conn.prepareStatement(query1);
                        if (id > 1) {
                            pStat1.setString(id - 1, form.getGrade());
                        }
                        pStat1.setInt(id, Integer.parseInt(form.getCourse()));
                    }
                }
            }
            log.info(query1);
            // execute query
            pStat1.executeUpdate();
            log.info("Stop View Course");
            log.info("Start Create View Project");
            id = 1;
            pStat1 = null;
            query1 = "";
            query1 = "DROP VIEW IF EXISTS prj_view";
            pStat1 = conn.prepareStatement(query1);
            pStat1.executeUpdate();
            pStat1 = null;
            query1 = "create view prj_view as select e.EmpID,p.ProjectID "
                    + "from employee e, Project p, projectexp pe "
                    + "where e.EmpID=pe.EmpID and p.ProjectID=pe.ProjectID ";
            if (form.getPrjCom().equals("0")) {
                pStat1 = conn.prepareStatement(query1);
            } else {
                if (form.getPrjDept().equals("0")) {
                    query1 += " and p.DepartID in (select d.DepartID from department d where d.CompanyID=?)";
                    pStat1 = conn.prepareStatement(query1);
                    pStat1.setInt(1, Integer.parseInt(form.getPrjCom()));
                } else {
                    if (form.getPrj().equals("0")) {
                        query1 += " and p.DepartID =?";
                        pStat1 = conn.prepareStatement(query1);
                        pStat1.setInt(1, Integer.parseInt(form.getPrjDept()));
                    } else {
                        query1 += " and p.ProjectID =?";
                        pStat1 = conn.prepareStatement(query1);
                        pStat1.setInt(1, Integer.parseInt(form.getPrj()));
                    }
                }
            }
            log.info(query1);
            // execute query
            pStat1.executeUpdate();
            log.info("Stop View Project");

            log.info("Start Tech View");
            final String txtlang = form.getLangtxt();
            final String txtapp = form.getApptxt();
            final String txttech = form.getTechtxt();
            final String txtfrw = form.getFrwtxt();

            if (!txtlang.equals("") && txtlang != null) {
                getlang = true;
                final String[] temp = txtlang.split("\\;");
                for (int i = 0; i < temp.length; i++) {
                    log.info("a" + temp[i]);
                    final String[] temp1 = temp[i].split("\\|");
                    query1 = "SELECT e.EmpID "
                             + "FROM Employee e, projectexp pv, project_language pl, project p "
                             + "where p.ProjectID=pv.ProjectID and pv.ProjectID=pl.ProjectID and "   
                             + "e.EmpID=pv.EmpID and pl.LangID=? and "   
                             + "(DATEDIFF(STR_TO_DATE(p.periodTo,'%d/%m/%Y')"
                             + ",STR_TO_DATE(p.periodFrom,'%d/%m/%Y')))/30 >=?";   
    
                    pStat1 = conn.prepareStatement(query1);
    
                    pStat1.setInt(1, Integer.parseInt(temp1[0]));
                    pStat1.setInt(2, Integer.parseInt(temp1[1]));
                    
                    final ResultSet r1 = pStat1.executeQuery();
                    while (r1.next()) {
                        if (empid.indexOf(r1.getInt("Empid")) < 0) {
                            empid.add(r1.getInt("EmpID"));
                        }
                    }

                }
            }
            List<Integer> tempempid = new ArrayList<Integer>();
            if (!txtapp.equals("") && txtapp != null) {
                getlang = true;
                final String[] temp = txtapp.split("\\;");
                for (int i = 0; i < temp.length; i++) {
                    log.info("a" + temp[i]);
                    final String[] temp1 = temp[i].split("\\|");
                    query1 = "SELECT e.EmpID "
                            + "FROM Employee e, projectexp pv, project_application pl, project p "
                            + "where p.ProjectID=pv.ProjectID and pv.ProjectID=pl.ProjectID and "
                            + "e.EmpID=pv.EmpID and pl.AppID=? and "
                            + "(DATEDIFF(STR_TO_DATE(p.periodTo,'%d/%m/%Y'),"
                            + "STR_TO_DATE(p.periodFrom,'%d/%m/%Y')))/30 >=?";

                    pStat1 = conn.prepareStatement(query1);

                    pStat1.setInt(1, Integer.parseInt(temp1[0]));
                    pStat1.setInt(2, Integer.parseInt(temp1[1]));
                    final ResultSet r1 = pStat1.executeQuery();
                    while (r1.next()) {
                        if (tempempid.indexOf(r1.getInt("Empid")) < 0) {
                            tempempid.add(r1.getInt("EmpID"));
                        }
                    }

                }
            }
            
            empid = this.joinlst(empid, tempempid);
            
            tempempid = new ArrayList<Integer>();
            if (!txttech.equals("") && txttech != null) {
                getlang = true;
                final String[] temp = txttech.split("\\;");
                for (int i = 0; i < temp.length; i++) {
                    log.info("a" + temp[i]);
                    final String[] temp1 = temp[i].split("\\|");
                    query1 = "SELECT e.EmpID "
                            + "FROM Employee e, projectexp pv, project_technology pl, project p "
                            + "where p.ProjectID=pv.ProjectID and pv.ProjectID=pl.ProjectID and "
                            + "e.EmpID=pv.EmpID and pl.TechID=? and "
                            + "(DATEDIFF(STR_TO_DATE(p.periodTo,'%d/%m/%Y'),"
                            + "STR_TO_DATE(p.periodFrom,'%d/%m/%Y')))/30 >=?";

                    pStat1 = conn.prepareStatement(query1);

                    pStat1.setInt(1, Integer.parseInt(temp1[0]));
                    pStat1.setInt(2, Integer.parseInt(temp1[1]));
                    final ResultSet r1 = pStat1.executeQuery();
                    while (r1.next()) {
                        if (tempempid.indexOf(r1.getInt("Empid")) < 0) {
                            tempempid.add(r1.getInt("EmpID"));
                        }
                    }

                }
            }
            empid = this.joinlst(empid, tempempid);
            for (int i = 0; i < empid.size(); i++) {
                log.info("arr " + empid.get(i));
            }
            tempempid = new ArrayList<Integer>();
            if (!txtfrw.equals("") && txtfrw != null) {
                getlang = true;
                final String[] temp = txtfrw.split("\\;");
                for (int i = 0; i < temp.length; i++) {
                    log.info("a" + temp[i]);
                    final String[] temp1 = temp[i].split("\\|");
                    query1 = "SELECT e.EmpID "
                            + "FROM Employee e, projectexp pv, project_framework pl, project p "
                            + "where p.ProjectID=pv.ProjectID and pv.ProjectID=pl.ProjectID and "
                            + "e.EmpID=pv.EmpID and pl.FrameID=? and "
                            + "(DATEDIFF(STR_TO_DATE(p.periodTo,'%d/%m/%Y'),"
                            + "STR_TO_DATE(p.periodFrom,'%d/%m/%Y')))/30 >=?";

                    pStat1 = conn.prepareStatement(query1);

                    pStat1.setInt(1, Integer.parseInt(temp1[0]));
                    pStat1.setInt(2, Integer.parseInt(temp1[1]));
                    final ResultSet r1 = pStat1.executeQuery();
                    while (r1.next()) {
                        if (tempempid.indexOf(r1.getInt("Empid")) < 0) {
                            tempempid.add(r1.getInt("EmpID"));
                        }
                    }

                }
            }
            empid = this.joinlst(empid, tempempid);
            for (int i = 0; i < empid.size(); i++) {
                log.info("arr " + empid.get(i));
            }
            log.info("Stop Tech View");
            // query command

            query = "select distinct e.EmpID,Name,Birthday,Address,Email,Phone "
                    + "from employee e,department d where e.DepartID=d.DepartID";
            if (form.getCompIssued().equals("0")
                    && form.getGrade().equals("All")) {
                if (form.getPrjCom().equals("0")) {

                } else {
                    query += " and e.EmpID in (select pv.EmpID from prj_view pv)";
                }
            } else {
                if (form.getPrjCom().equals("0")) {

                    query += " and e.EmpID in (select cv.EmpID from course_view cv)";
                } else {
                    query += " and e.EmpID in (select cv.EmpID from course_view cv,prj_view pv "
                            + "where cv.EmpID=pv.EmpID)";
                }
            }

            // get connection

            // prepare statement
            // Query Statement for first part EmpName, Comp, Department
            id = 1;
            if (form.getEmpName().length() == 0) {
                pStat = conn.prepareStatement(query);
            } else {
                query += " and e.Name like " + "'%" + form.getEmpName() + "%'" ;
                pStat = conn.prepareStatement(query);
                id++;

            }

            if (form.getComp().equals("0")) {

            } else {
                if (form.getDep().equals("0")) {
                    query += " and d.CompanyID=?";
                    pStat = conn.prepareStatement(query);
                    if (id > 1) {
                        pStat.setString(id - 1, form.getEmpName());
                    }
                    pStat.setInt(id, Integer.parseInt(form.getComp()));
                } else {
                    query += " and e.DepartID=?";
                    pStat = conn.prepareStatement(query);
                    if (id > 1) {
                        pStat.setString(id - 1, form.getEmpName());
                    }
                    pStat.setInt(id, Integer.parseInt(form.getDep()));
                }
            }
            log.info(query);

            rs = pStat.executeQuery();
            // get result
            for (int i = 0; i < empid.size(); i++) {
                log.info("arr " + empid.get(i));
            }
            while (rs.next()) {
                log.info(rs.getInt("EmpID"));
                if (getlang) {
                    if (empid.indexOf(rs.getInt("EmpID")) >= 0) {
                        dto = new EmployeeDTO();
                        dto.setId(rs.getInt("EmpID"));
                        dto.setName(rs.getString("Name"));
                        dto.setBirthday(rs.getString("Birthday"));
                        dto.setAddress(rs.getString("Address"));
                        dto.setEmail(rs.getString("Email"));
                        dto.setPhone(rs.getString("Phone"));
                        result.add(dto);
                    }
                } else {
                    dto = new EmployeeDTO();
                    dto.setId(rs.getInt("EmpID"));
                    dto.setName(rs.getString("Name"));
                    dto.setBirthday(rs.getString("Birthday"));
                    dto.setAddress(rs.getString("Address"));
                    dto.setEmail(rs.getString("Email"));
                    dto.setPhone(rs.getString("Phone"));
                    result.add(dto);
                }
            }

            // request.getPortletSession().setAttribute("CompanyList", result,
            // PortletSession.APPLICATION_SCOPE);
        } catch (final Exception sqlEx) {
            log.error("SearchInfoDAO.getResult " + sqlEx);
        } finally {
            try {
                rs.close();
                pStat.close();
                conn.close();
            } catch (final SQLException sqlEx) {
                log.error("SearchInfoDAO.getResult " + sqlEx);
            }
        }

        return result;
    }
}
